package edu.myown.node;

import java.util.ArrayList;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcNodeStorage implements NodeStorage{
	
	private class NodeAttrib {
		
		int id, pid;
		String name;
		
		public void setId(int id) {
			this.id = id;
		}
		public void setName(String name) {
			this.name = name;
		}
		public void setPid(int pid) {
			this.pid = pid;
		}
		
		public int getId() {
			return id;
		}
		public String getName() {
			return name;
		}
		public int getPid() {
			return pid;
		}
		
	}

	private static void addChild(Node n, ArrayList <NodeAttrib> alist, int id){
		
		for (int a=0; a < alist.size(); a++) {
			  
			int nodepid =alist.get(a).getPid();
						
			if (nodepid == id) {
					
					String nodename = alist.get(a).getName();
					int nodeid = alist.get(a).getId();
					Node $nodename = new Node();
					$nodename.setData(nodename);
					n.getChildNodes().add($nodename);
					addChild($nodename, alist, nodeid);
			}
							
		}
	}
	
	public Connection connectMysql(){
		
		System.out.println("MySQL JDBC Connection: Connecting ...");
		 
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println("Where is your MySQL JDBC Driver?");
			e.printStackTrace();
		}
	 
		System.out.println("MySQL JDBC Driver Registered!");
		Connection connection = null;
	 
		try {
			connection = DriverManager
						.getConnection("jdbc:mysql://localhost:3306/nodebase","root","96111");
	 
		} catch (SQLException e) {
			System.out.println("Connection Failed! Check output console");
			e.printStackTrace();
		}
	 
		if (connection != null) {
			System.out.println("Connected succefully!");
		} else {
			System.out.println("Failed to make connection!");
		}
		
		return connection;
		
	}
	
	public Node read(){
	
	int rsid, rspid;
	String rsdata;
	
	Node rnode = new Node();
	ArrayList <NodeAttrib> list = new ArrayList <NodeAttrib>();	
		
	try{
		
		Connection connection = connectMysql();
	 
		String query = "Select * FROM mainstorage";
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		
		while (rs.next()) {
			
			rsid = Integer.valueOf(rs.getString(1));
			rsdata = rs.getString(2);
			if (rs.getString(3) != null) {
				rspid = Integer.valueOf(rs.getString(3));
			} else {
				rspid = 0;
			}
		//	System.out.println("Rewriting:"+ rsid + "-" + rsdata + "-" + rspid);
			NodeAttrib $rsdata = new NodeAttrib();
			$rsdata.setId(rsid);
			$rsdata.setName(rsdata);
			$rsdata.setPid(rspid);
			list.add($rsdata);
						
		} 
		connection.close();
		
		rnode.setData("root");
		addChild(rnode, list, 1);
		
	}
	catch (SQLException e){
		e.printStackTrace();
	}
	return rnode;
}
	
private static void writeChild(int id, Node n, Statement stmt) throws SQLException{
		if (n.getChildNodes().size() != 0) {
			
			for (int aa = 0; aa < n.getChildNodes().size(); aa++) {
					
				String queryupdate = "INSERT INTO mainstorage"
						+ "(DATA, PARENT_NODE_ID) " + "VALUES"
						+ "('"+n.getChildNodes().get(aa).getData()+"', "+id+");";
				
				stmt.executeUpdate(queryupdate);
				ResultSet newpidrs = stmt.executeQuery("SELECT MAX(id) FROM mainstorage");;
				newpidrs.next();
				int newpid	= Integer.valueOf(newpidrs.getString(1)); 	
			//	System.out.println(queryupdate+"..."+newpid);
				
				writeChild(newpid, n.getChildNodes().get(aa), stmt);
				
				}
		}
		else {
		}
	} 
	
	
	public void store(Node n) {
		
		try{
		
			Connection connection = connectMysql();
		 
			Statement stmt = connection.createStatement();
		//	stmt.executeUpdate("drop table `nodebase`.`mainstorage`;");
		//	stmt.executeUpdate("CREATE  TABLE `nodebase`.`mainstorage` (`ID` INT NOT NULL AUTO_INCREMENT ,`DATA` VARCHAR(45) NOT NULL ,`PARENT_NODE_ID` INT NULL ,PRIMARY KEY (`ID`) );");
			stmt.executeUpdate("truncate table `nodebase`.`mainstorage`;");
			stmt.executeUpdate("INSERT INTO mainstorage"
					+ "(ID, DATA, PARENT_NODE_ID) " + "VALUES"
					+ "(1, 'root', null);");
			writeChild(1, n, stmt);
			
			
			connection.close();
			
		}
		catch (SQLException e){
			e.printStackTrace();
		}		
		
	}

} 